package com.hkw.dao.impl;

import com.hkw.dao.IClassRoomDao;
import com.hkw.model.ClassRoom;
import com.hkw.utils.*;
import com.mysql.jdbc.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * IClassRoomDao的实现类
 */
public class ClassRoomDaoImpl implements IClassRoomDao {
    @Override
    public List<ClassRoom> findAll(PagesUtils pagesUtils) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        //判断当前所在页是否超过最大页数，如果超过，则将当前所在页变为最大页数
        if(pagesUtils.getNowPage()>pagesUtils.getPages()){
            pagesUtils.setNowPage(pagesUtils.getPages());
        }
        //判断当前所在页是否小于1，如果小，则将当前所在页变为1
        if(pagesUtils.getNowPage()<1){
            pagesUtils.setNowPage(1);
        }
        String sql = "select * from classroom limit ?,?";
        //使用匿名内部类自己手动将查询数据封装成一个list
        List<ClassRoom> list = qr.query(con, sql, new ResultSetHandler<List<ClassRoom>>() {
            List<ClassRoom> list = new ArrayList<>();
            @Override
            public List<ClassRoom> handle(ResultSet resultSet) throws SQLException {
                while(resultSet.next()) {
                    ClassRoom classRoom = new ClassRoom();
                    classRoom.setId(resultSet.getInt("id"));
                    classRoom.setClassTeaName(resultSet.getString("classTeaName"));
                    classRoom.setGrade(resultSet.getString("grade"));
                    classRoom.setName(resultSet.getString("name"));
                    classRoom.setNumber(resultSet.getInt("number"));
                    classRoom.setSlogan(resultSet.getString("slogan"));
                    list.add(classRoom);
                }
                return list;
            }
        },pagesUtils.getBeginrow(),pagesUtils.getMaxrows());
        con.close();
        return list;
    }

    @Override
    public int findAllRows() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String oldsql = "select count(id) from classroom";
        Object ob = qr.query(con, oldsql, new ResultSetHandler<Object>() {
            @Override
            public Object handle(ResultSet resultSet) throws SQLException {
                Object ob = null;
                while (resultSet.next()) {
                    ob = resultSet.getInt(1);
                }
                return ob;
            }
        });
        int total = (Integer)ob;
        con.close();  //释放连接
        return total;
    }

    @Override
    public int insertClassRoom(ClassRoom classRoom) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String sql = "insert into classroom(name,grade,classTeaName,slogan,number) values(?,?,?,?,?)";
        int update = qr.update(con, sql, classRoom.getName(),classRoom.getGrade(),classRoom.getClassTeaName(),classRoom.getSlogan(),0);
        con.close();
        return update;
    }

    @Override
    public List<ClassRoom> findAllId() throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String sql = "select id,name from classroom";
        List<ClassRoom> list = new ArrayList<>();
        qr.query(con, sql, new ResultSetHandler<List<ClassRoom>>() {
            @Override
            public List<ClassRoom> handle(ResultSet resultSet) throws SQLException {
                while(resultSet.next()) {
                    ClassRoom classRoom = new ClassRoom();
                    classRoom.setId(resultSet.getInt("id"));
                    classRoom.setName(resultSet.getString("name"));
                    list.add(classRoom);
                }
                return list;
            }
        });
        con.close();
        return list;
    }

    /**
     * 删除教室，删除要判断一下，教室里是否还有人
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public int delClassById(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String sql = "select * from classroom where id = ?";
        ClassRoom query = qr.query(con, sql, new ClassRoomHandler(), id);
        Integer number = query.getNumber();
        if(number > 0){
            return -1;
        }else{
            String sql2 = "delete from classroom where id = ?";
            int update = qr.update(con, sql2, id);
            if(update == 1){
                return update;
            }
        }
        con.close();
        return 0;
    }

    @Override
    public ClassRoom findClassRoomById(int id) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String sql = "select * from classroom where id = ?";
        ClassRoom classRoom = qr.query(con, sql, new ClassRoomHandler(), id);
        con.close();
        return classRoom;
    }

    @Override
    public int updateClassRoom(ClassRoom classRoom) throws SQLException {
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        String sql = "update classroom set name =?,grade=?,classTeaName=?,slogan=? where id = ?";
        int update = qr.update(con, sql, classRoom.getName(), classRoom.getGrade(), classRoom.getClassTeaName(), classRoom.getSlogan(), classRoom.getId());
        return update;
    }

    /**
     * 用name，grade，classTeaName来查询教室
     * @param classRoom
     * @return
     * @throws SQLException
     */
    @Override
    public List<ClassRoom> findClassRoomByClassRoom(ClassRoom classRoom) throws SQLException {
        String name = classRoom.getName();
        String grade = classRoom.getGrade();
        String classTeaName = classRoom.getClassTeaName();
        QueryRunner qr = new QueryRunner(DruidUtils.getDataSource());
        Connection con = DruidUtils.getConnection();
        StringBuffer sql = new StringBuffer("select * from classroom ");
        List<ClassRoom> list = new ArrayList<>();
        if(!StringUtils.isNullOrEmpty(name) && !StringUtils.isNullOrEmpty(grade) && !StringUtils.isNullOrEmpty(classTeaName)){
            sql.append("where name = ? and grade = ? and classTeaName =?");
            list = qr.query(con, sql.toString(), new ArrayClassRoomHandler(), name, grade,classTeaName);
        }else if(!StringUtils.isNullOrEmpty(name) && !StringUtils.isNullOrEmpty(grade)){
            sql.append("where name = ? and grade = ?");
            list = qr.query(con,sql.toString(),new ArrayClassRoomHandler(),name,grade);
        }else if(!StringUtils.isNullOrEmpty(name) && !StringUtils.isNullOrEmpty(classTeaName)){
            sql.append("where name = ? and classTeaName = ?");
            list = qr.query(con,sql.toString(),new ArrayClassRoomHandler(),name,classTeaName);
        }else if(!StringUtils.isNullOrEmpty(grade) && !StringUtils.isNullOrEmpty(classTeaName)){
            sql.append("where grade = ? and classTeaName = ?");
            list = qr.query(con,sql.toString(),new ArrayClassRoomHandler(),grade,classTeaName);
        }else if(!StringUtils.isNullOrEmpty(name)){
            sql.append("where name = ?");
            list = qr.query(con,sql.toString(),new ArrayClassRoomHandler(),name);
        }else if(!StringUtils.isNullOrEmpty(grade)){
            sql.append("where grade = ?");
            list = qr.query(con,sql.toString(),new ArrayClassRoomHandler(),grade);
        }else if(!StringUtils.isNullOrEmpty(classTeaName)){
            sql.append("where classTeaName = ?");
            list = qr.query(con,sql.toString(),new ArrayClassRoomHandler(),classTeaName);
        }else{
            list = qr.query(con,sql.toString(),new ArrayClassRoomHandler());
        }
        con.close();
        return list;
    }
}
